# 添加异常检测代码
import json
from urllib.parse import quote_plus

import pandas as pd

# 创建一个示例数据框
# data = {
#     'Category': ['A', 'A', 'B', 'B', 'A', 'B'],
#     'Values': [10, 20, 30, 40, 50, 60],
#     'Values1': [10, 20, 30, 40, 50, 60],
# }
# df = pd.DataFrame(data)
#
# # 打印原始数据框
# print("原始数据框:")
# print(df)
#
# # 根据'Category'字段进行分组，并对'Values'字段进行累加
# grouped = df.groupby('Category')[['Values',"Values1"]].sum().reset_index()
#
# # 打印累加后的数据框
# print("\n累加后的数据框:")
# print(grouped)


import pandas as pd
import numpy as np
from sqlalchemy import create_engine


def mysql_config():
    user = 'root'
    password = quote_plus('Jison0820')
    # passwd ='merchantsasd123!@%&'
    host = 'rm-bp1be6s3581xp13rfco.mysql.rds.aliyuncs.com'
    # port1 ='3306'
    dbname2 = 'data-warehouse'
    engine2 = create_engine(f"mysql+pymysql://{user}:{password}@{host}:3306/{dbname2}?charset=utf8mb4")
    return engine2



def seven_huizong_data():
    df = pd.read_sql("select * from data_warehouse_douyin_video_by_seven_data",mysql_config())
    df = df.drop(["cover_click_rate","cover_setting_rate"],axis=1)
    df['date'] = pd.to_datetime(df['date'], format="%Y-%m-%d")

    grouped_sum = df.groupby('douyin_code')[["play_count","works_like","works_share","works_comment","net_growth_fans","take_off_fans","homepage_access"]].sum().reset_index()

    idx = df.groupby('douyin_code')['date'].idxmax()

    max_time_rows = df.loc[idx]

    max_time_values = max_time_rows[['douyin_code', 'total_fans']].reset_index(drop=True)
    # print(max_time_values)
    # 步骤3：合并结果
    final_result = pd.merge(grouped_sum, max_time_values, on='douyin_code')

    print(final_result)
    return final_result